/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package verins.DAO;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import verins.Utils.Conexion;
import verins.beans.Persona;
import verins.beans.Usuario;

/**
 *
 * @author hectdx
 */
public class PersonaDAO {
    
    
    public Persona findByDNI(String DNI){
        List lista=null;
        Persona pers=null;
        try{
            
            EntityManagerFactory emf = Persistence.createEntityManagerFactory("verinsPU");
            EntityManager em = emf.createEntityManager();
            
            Query query=em.createQuery("SELECT p FROM Persona p WHERE p.dni=?1") ;
            query.setParameter(1, DNI);
            lista=query.getResultList();
            em.close();    
            emf.close();
            
            if (!lista.isEmpty())
                pers=(Persona)lista.get(0);
            
            
        }catch(Exception ex){
            System.out.println(lista!=null?lista.size():"-"+ex.getMessage());
        }
        return pers;
    }
    
    public boolean save(Persona persona){
    boolean bOk=false;
    
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("verinsPU");
        EntityManager em = emf.createEntityManager();
       
        em.getTransaction().begin();
        em.merge(persona);
        em.getTransaction().commit();
        em.close();
        emf.close();
    
    bOk=true;
    return bOk;
    
    }
    
    public  List findByCriterios(Persona personabusq){
       List lista=null;
       try{
          
           String sSql="SELECT p FROM Persona p WHERE 1=1  ";
                    if (personabusq.getNombres().trim().length()>0)
                    sSql+= "and  upper(p.nombres)  like ?1   ";
                    if (personabusq.getApellidopaterno().trim().length()>0)
                    sSql+= "and  upper(p.apellidopaterno) like ?2   ";
                    if (personabusq.getApellidomaterno().trim().length()>0)
                    sSql+= "and  upper(p.apellidomaterno) like ?3   ";
                    
            EntityManagerFactory emf = Persistence.createEntityManagerFactory("verinsPU");
            EntityManager em = emf.createEntityManager();
            
            Query query=em.createQuery(sSql) ;
            if (personabusq.getNombres().trim().length()>0)
            query.setParameter(1, "%"+personabusq.getNombres().toUpperCase()+"%");
            if (personabusq.getApellidopaterno().trim().length()>0)
            query.setParameter(2, "%"+personabusq.getApellidopaterno().toUpperCase()+"%");
            if (personabusq.getApellidomaterno().trim().length()>0)
            query.setParameter(3, "%"+personabusq.getApellidomaterno().toUpperCase()+"%");
           
                    
            lista=query.getResultList();
            em.close();    
            emf.close();
            
            return lista;
            
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }        
        
        return lista;
    
    }
    
     public  List<Usuario> findDigitadores(){
       List lista=new ArrayList<Usuario>();
       PreparedStatement pstmt=null;
       ResultSet rset=null;
       try{
          
           String sSql=" select p.dni from verinscribe.usuario u inner join "+
                        " verinscribe.persona p on u.dni=p.dni and u.idrol=2";
                  
           Conexion con=new Conexion();
           pstmt=con.getConexion().prepareStatement(sSql);
           rset=pstmt.executeQuery();
           
           Usuario us=null;
           while(rset.next()){
               us=new Usuario(rset.getInt(1));
               lista.add(us);
           }
           
            return lista;
            
        }catch(Exception ex){
            System.out.println(ex.getMessage());
        }        
        
        return lista;
    
    }
}
